package ua.kharkov.khpi.tarasenko.bach.dao.mysql.order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import org.apache.log4j.Logger;

import ua.kharkov.khpi.tarasenko.bach.core.Constant;
import ua.kharkov.khpi.tarasenko.bach.core.db.DBUtils;
import ua.kharkov.khpi.tarasenko.bach.core.db.EShopDataStoreException;
import ua.kharkov.khpi.tarasenko.bach.dao.core.order.OrderUserDAO;
import ua.kharkov.khpi.tarasenko.bach.dao.core.order.OrderUserDTO;

/**
 * DAO for working with table with user's orders in Derby database.
 * 
 * @author oleksandr
 * 
 */
public class MysqlOrderCustomerDAO implements OrderUserDAO {
	private static final Logger log = Logger.getLogger(MysqlOrderCustomerDAO.class);

	// ===========================================================================
	// Tables column's names
	// ===========================================================================
	private static final String COLUMN_ORDER_ID = "order_id";
	private static final String COLUMN_CUSTOMER_ID = "customer_id";
	private static final String COLUMN_ORDER_DATE = "order_date";
	private static final String COLUMN_ORDER_STATUS = "order_status";

	// ===========================================================================
	// QUERIES
	// ===========================================================================
	private static final String QUERY_CREATE_ORDER = "INSERT INTO "
			+ Constant.TABLE_ORDER_CUSTOMER + "(" + COLUMN_CUSTOMER_ID + ", "
			+ COLUMN_ORDER_DATE + ", " + COLUMN_ORDER_STATUS
			+ ") VALUES(?,?,?)";

	private static final String QUERY_FIND_ORDERS_BY_USER_ID = "SELECT * FROM "
			+ Constant.TABLE_ORDER_CUSTOMER + " WHERE " + COLUMN_CUSTOMER_ID + "=?";

	private static final String QUERY_FIND_ALL_ORDERS = "SELECT * FROM "
			+ Constant.TABLE_ORDER_CUSTOMER + "";

	private static final String QUERY_FIND_ORDER_BY_ID = "SELECT * FROM "
			+ Constant.TABLE_ORDER_CUSTOMER + " WHERE " + COLUMN_ORDER_ID + "=?";

	private static final String QUERY_FIND_ORDERS_IDS = "SELECT "
			+ COLUMN_ORDER_ID + " FROM " + Constant.TABLE_ORDER_CUSTOMER + "";

	private static final String QUERY_UPDATE_ORDER_USER_STATUS = "UPDATE "
			+ Constant.TABLE_ORDER_CUSTOMER + " SET " + COLUMN_ORDER_STATUS
			+ "=? WHERE " + COLUMN_ORDER_ID + "=? AND " + COLUMN_CUSTOMER_ID
			+ "=?";

	private Connection conn;

	/**
	 * Creates entity if the DerbyUserDAO.
	 * 
	 * @param conn
	 *            - connection to the database.
	 * @throws EShopDataStoreException
	 *             if connection is null.
	 */
	public MysqlOrderCustomerDAO(Connection conn)
			throws EShopDataStoreException {
		if (conn == null) {
			throw new EShopDataStoreException("Connection is null!");
		}
		this.conn = conn;
	}

	@Override
	public int addOrder(OrderUserDTO orderUserDTO)
			throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("Order to create: " + orderUserDTO);
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		int orderId = 0;
		try {
			ps = conn.prepareStatement(QUERY_CREATE_ORDER,
					Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1, orderUserDTO.getUserId());
			ps.setTimestamp(2, new java.sql.Timestamp(orderUserDTO
					.getOrderDate().getTime()));
			ps.setInt(3, orderUserDTO.getStatus().ordinal());
			int rowsAdded = ps.executeUpdate();
			if (log.isTraceEnabled()) {
				log.trace("Created " + rowsAdded + " orders.");
			}
			rs = ps.getGeneratedKeys();
			if (rs.next()) {
				orderId = rs.getInt(1);
			}
			if (log.isDebugEnabled()) {
				log.debug("Order's id=" + orderId);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during creating new order:\n"
							+ orderUserDTO, e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return orderId;
	}

	@Override
	public ArrayList<OrderUserDTO> findOrdersByUserId(int userId)
			throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("User's id to find orders is " + userId);
		}

		ArrayList<OrderUserDTO> orders = new ArrayList<OrderUserDTO>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ORDERS_BY_USER_ID);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			OrderUserDTO orderDTO = null;
			while (rs.next()) {
				orderDTO = extractOrderUser(rs);
				orders.add(orderDTO);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for user's orders with id="
							+ userId, e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return orders;
	}

	@Override
	public int updateOrderUserStatus(OrderUserDTO orderUserDTO)
			throws EShopDataStoreException {
		int orderId = orderUserDTO.getOrderId();
		if (log.isTraceEnabled()) {
			log.trace("Order to update status: " + orderUserDTO);
		}

		PreparedStatement ps = null;
		if (orderId < 1) {
			throw new EShopDataStoreException(
					"Invalid order's or user's id to update status!");
		}
		int rowsUpdated = 0;
		try {
			ps = conn.prepareStatement(QUERY_UPDATE_ORDER_USER_STATUS);
			ps.setInt(1, orderUserDTO.getStatus().ordinal());
			ps.setInt(2, orderId);
			ps.setInt(3, orderUserDTO.getUserId());
			rowsUpdated = ps.executeUpdate();
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during updating order's status, id="
							+ orderId, e);
		} finally {
			DBUtils.close(null, ps);
		}
		return rowsUpdated;
	}

	private OrderUserDTO extractOrderUser(ResultSet rs) throws SQLException {
		OrderUserDTO orderDTO = new OrderUserDTO();
		orderDTO.setOrderId(rs.getInt(COLUMN_ORDER_ID));
		orderDTO.setUserId(rs.getInt(COLUMN_CUSTOMER_ID));
		Date orderDate = DBUtils.convertSqlDateToUtilDate(rs
				.getTimestamp(COLUMN_ORDER_DATE));
		orderDTO.setOrderDate(orderDate);
		orderDTO.setStatus(rs.getInt(COLUMN_ORDER_STATUS));
		return orderDTO;
	}

	@Override
	public ArrayList<OrderUserDTO> findAllOrders()
			throws EShopDataStoreException {

		ArrayList<OrderUserDTO> orders = new ArrayList<OrderUserDTO>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ALL_ORDERS);
			rs = ps.executeQuery();
			OrderUserDTO orderDTO = null;
			while (rs.next()) {
				orderDTO = extractOrderUser(rs);
				orders.add(orderDTO);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for all user's orders", e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return orders;
	}

	@Override
	public ArrayList<Integer> findAllOrdersIds()
			throws EShopDataStoreException {

		ArrayList<Integer> ordersIds = new ArrayList<Integer>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ORDERS_IDS);
			rs = ps.executeQuery();

			int orderId = 0;
			while (rs.next()) {
				orderId = rs.getInt(COLUMN_ORDER_ID);
				ordersIds.add(orderId);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for all user's orders", e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return ordersIds;
	}

	@Override
	public OrderUserDTO findOrderById(int orderId)
			throws EShopDataStoreException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		OrderUserDTO order = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ORDER_BY_ID);
			log.debug(QUERY_FIND_ORDER_BY_ID);
			ps.setInt(1, orderId);
			rs = ps.executeQuery();

			while (rs.next()) {
				orderId = rs.getInt(COLUMN_ORDER_ID);
				order = extractOrderUser(rs);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for all user's orders", e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return order;
	}
}
